package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.HisPrEntity;
import com.css.fxfzypg.modules.baseData.queryParams.EarthquakeDisasterQueryParams;
import com.css.fxfzypg.modules.baseData.repository.EarthquakeDisasterRepository;
import com.css.fxfzypg.modules.baseData.entity.EarthquakeDisasterEntity;
import com.css.fxfzypg.modules.baseData.vo.EarthquakeDisasterVO;
import com.css.fxfzypg.modules.similitudeAnalyse.entity.RealEarthquakeEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-17 17:14
 */
@Repository
public class EarthquakeDisasterRepositoryImpl implements EarthquakeDisasterRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public Boolean saveEarthquakeDisaster(EarthquakeDisasterEntity earthquakeDisasterEntity) {
        String sql = "";
        sql = "insert into data_earthquake_disaster values(:dedId, :dedName, :dedTime, :dedLocation, :dedLongitude, :dedLatitude, :dedEarthquake , " +
                ":dedDepth, :dedDeathToll, :dedCauseOfDeath, :dedNumberOfInjury, :dedNumberOfPlacement, :dedRescueForceInput, :dedInputOfMedicalForce, " +
                ":dedEconomicLosses, :dedInfrastructureDamage, :dedGeologicalHazardSituation, :dedSecondaryFire, :dedSecondaryFlood, :dedSecondaryExplosion, " +
                ":dedDangerousChemicalLeak, :dedCreatetime, :dedUpdatetime,:dedIsdelete, :dedCreateUser, :dedUpdateUser, :dedMacroscopicEpicenter, " +
                ":dedMaximumIntensity, :dedDisasterDescription, :remark, :enclosure, :typicalEarthquakeDamagePicture, :isoseismalLineAndIntensityPicture, :dedHour) ";

        SqlParameterSource source = new BeanPropertySqlParameterSource(earthquakeDisasterEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);

        return i > 0;
    }

    @Override
    public EarthquakeDisasterEntity getEarthquakeDisasterById(String dataEarthquakeDisasterId) {
        String sql = "";
        sql = "select  * from data_earthquake_disaster  where  ded_isdelete= '" + YNEnum.N.toString() + "' and ded_id = '" + dataEarthquakeDisasterId + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EarthquakeDisasterEntity.class));
    }

    @Override
    public Map<String, Object> getAllEarthquakeDisasterByParams(EarthquakeDisasterQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  * from data_earthquake_disaster  ");

        StringBuilder orderSql = new StringBuilder(" order by ded_createtime desc");

        StringBuilder whereSql = new StringBuilder(" where ded_isdelete = '" + YNEnum.N.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getDedEarthquake())) {
            whereSql.append(" and cast(ded_earthquake as varchar) like '%" + queryParams.getDedEarthquake() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getDedName())) {
            whereSql.append(" and ded_name like '%" + queryParams.getDedName() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getDedMacroscopicEpicenter())) {
            whereSql.append(" and ded_macroscopic_epicenter like '%" + queryParams.getDedMacroscopicEpicenter() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getDedTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and ded_time >= '" + Timestamp.valueOf(queryParams.getDedTime()) + "' and ded_time <= '" + Timestamp.valueOf(queryParams.getEndTime()) + "'");
        }

        int firstIndex = (queryParams.getCurPage() - 1) * queryParams.getPageSize();
        int lastIndex = queryParams.getPageSize();
        sql.append(whereSql).append(orderSql).toString();
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(EarthquakeDisasterEntity.class)).size();
        List<EarthquakeDisasterEntity> anrrsmEntities = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(EarthquakeDisasterEntity.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", anrrsmEntities);
        return map;
    }

    @Override
    public Boolean updateEarthquakeDisaster(EarthquakeDisasterEntity earthquakeDisasterEntity) {
        String sql = "";
        sql = "update data_earthquake_disaster set ded_name = :dedName, ded_time = :dedTime, ded_location = :dedLocation, " +
                "ded_longitude = :dedLongitude, ded_latitude = :dedLatitude, ded_earthquake = :dedEarthquake, ded_depth = :dedDepth, " +
                " ded_death_toll = :dedDeathToll, ded_cause_of_death = :dedCauseOfDeath, ded_number_of_injury = :dedNumberOfInjury, " +
                "ded_number_of_placement = :dedNumberOfPlacement, ded_rescue_force_input = :dedRescueForceInput, ded_input_of_medical_force = :dedInputOfMedicalForce, " +
                "ded_economic_losses = :dedEconomicLosses, ded_infrastructure_damage = :dedInfrastructureDamage, ded_geological_hazard_situation = :dedGeologicalHazardSituation," +
                " ded_secondary_fire = :dedSecondaryFire  ,ded_secondary_flood = :dedSecondaryFlood ,ded_secondary_explosion=:dedSecondaryExplosion, " +
                "ded_dangerous_chemical_leak =:dedDangerousChemicalLeak,ded_updatetime =:dedUpdatetime, ded_update_user =:dedUpdateUser, " +
                "ded_macroscopic_epicenter =:dedMacroscopicEpicenter, ded_maximum_intensity =:dedMaximumIntensity, ded_disaster_description =:dedDisasterDescription, " +
                "remark =:remark, enclosure =:enclosure, typical_earthquake_damage_picture =:typicalEarthquakeDamagePicture, isoseismal_line_and_intensity_picture =:isoseismalLineAndIntensityPicture, ded_hour =:dedHour " +
                "where ded_id = '" + earthquakeDisasterEntity.getDedId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(earthquakeDisasterEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i > 0;
    }

    @Override
    public Boolean deleteEarthquakeDisaster(List<String> ids) {
        String sql = "update data_earthquake_disaster set ded_isdelete = '" + YNEnum.Y.toString() + "'  where ded_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        int i = namedParameterJdbcTemplate.update(sql, map);
        return i > 0;
    }

    @Override
    public List<EarthquakeDisasterEntity> exportExcel() {
        return jdbcTemplate.query("select  u.* from data_earthquake_disaster u where u.ded_isdelete='" + YNEnum.N.toString() + "'", new BeanPropertyRowMapper<>(EarthquakeDisasterEntity.class));
    }

    @Override
    public void importExcel(List<EarthquakeDisasterEntity> earthquakeDisasterEntities) {
        jdbcTemplate.batchUpdate("insert into data_earthquake_disaster (ded_id,ded_name,ded_time,ded_location,ded_longitude,ded_latitude,ded_earthquake ,ded_depth,ded_death_toll,ded_cause_of_death,ded_number_of_injury,ded_number_of_placement,ded_rescue_force_input,ded_input_of_medical_force,ded_economic_losses,ded_infrastructure_damage,ded_geological_hazard_situation,ded_secondary_fire,ded_secondary_flood,ded_secondary_explosion,ded_dangerous_chemical_leak,ded_createtime,ded_isdelete,ded_create_user) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, earthquakeDisasterEntities.get(i).getDedId());
                ps.setString(2, earthquakeDisasterEntities.get(i).getDedName());
                java.sql.Date dedTime = new java.sql.Date(earthquakeDisasterEntities.get(i).getDedTime().getTime());
                ps.setDate(3, dedTime);
                ps.setString(4, earthquakeDisasterEntities.get(i).getDedLocation());
                ps.setBigDecimal(5, earthquakeDisasterEntities.get(i).getDedLongitude());
                ps.setBigDecimal(6, earthquakeDisasterEntities.get(i).getDedLatitude());
                ps.setBigDecimal(7, earthquakeDisasterEntities.get(i).getDedEarthquake());
                ps.setBigDecimal(8, earthquakeDisasterEntities.get(i).getDedDepth());
                ps.setString(9, earthquakeDisasterEntities.get(i).getDedDeathToll());
                ps.setString(10, earthquakeDisasterEntities.get(i).getDedCauseOfDeath());
                ps.setString(11, earthquakeDisasterEntities.get(i).getDedNumberOfInjury());
                ps.setString(12, earthquakeDisasterEntities.get(i).getDedNumberOfPlacement());
                ps.setString(13, earthquakeDisasterEntities.get(i).getDedRescueForceInput());
                ps.setString(14, earthquakeDisasterEntities.get(i).getDedInputOfMedicalForce());
                ps.setBigDecimal(15, earthquakeDisasterEntities.get(i).getDedEconomicLosses());
                ps.setString(16, earthquakeDisasterEntities.get(i).getDedInfrastructureDamage());
                ps.setString(17, earthquakeDisasterEntities.get(i).getDedGeologicalHazardSituation());
                ps.setString(18, earthquakeDisasterEntities.get(i).getDedSecondaryFire());
                ps.setString(19, earthquakeDisasterEntities.get(i).getDedSecondaryFlood());
                ps.setString(20, earthquakeDisasterEntities.get(i).getDedSecondaryExplosion());
                ps.setString(21, earthquakeDisasterEntities.get(i).getDedDangerousChemicalLeak());
                java.sql.Date createDate = new java.sql.Date(earthquakeDisasterEntities.get(i).getDedCreatetime().getTime());
                ps.setDate(22, createDate);
                ps.setString(23, earthquakeDisasterEntities.get(i).getDedIsdelete());
                ps.setString(24, earthquakeDisasterEntities.get(i).getDedCreateUser());
            }

            @Override
            public int getBatchSize() {
                return earthquakeDisasterEntities.size();
            }
        });
    }

    @Override
    public List<EarthquakeDisasterEntity> earthquakeSelect(RealEarthquakeEntity realEarthquake) {
        StringBuilder sql = new StringBuilder(" select  u.* from data_earthquake_disaster u ");
        StringBuilder whereSql = new StringBuilder(" where  u.ded_isdelete = '" + YNEnum.N.toString() + "'");

        String wholeSql = sql.append(whereSql).toString();
        List<EarthquakeDisasterEntity> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(EarthquakeDisasterEntity.class));
        return list;
    }

    @Override
    public List<EarthquakeDisasterVO> findFormDataList(EarthquakeDisasterQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select ded_name,ded_time,ded_location,ded_longitude,ded_latitude,ded_earthquake" +
                ",ded_depth,ded_death_toll,ded_cause_of_death,ded_number_of_injury,ded_number_of_placement,ded_rescue_force_input," +
                "ded_input_of_medical_force,ded_economic_losses,ded_infrastructure_damage,ded_geological_hazard_situation,ded_secondary_fire," +
                "ded_secondary_flood,ded_secondary_explosion,ded_dangerous_chemical_leak,ded_hour,ded_macroscopic_epicenter,ded_maximum_intensity," +
                "ded_disaster_description,remark from data_earthquake_disaster");

        StringBuilder orderSql = new StringBuilder(" order by ded_createtime desc");

        StringBuilder whereSql = new StringBuilder(" where ded_isdelete = '" + YNEnum.N.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getDedEarthquake())) {
            whereSql.append(" and ded_earthquake ='" + queryParams.getDedEarthquake() + "'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getDedName())) {
            whereSql.append(" and ded_name like '%" + queryParams.getDedName() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getDedMacroscopicEpicenter())) {
            whereSql.append(" and ded_macroscopic_epicenter like '%" + queryParams.getDedMacroscopicEpicenter() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getDedTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and ded_time >= '" + Timestamp.valueOf(queryParams.getDedTime()) + "' and ded_time <= '" + Timestamp.valueOf(queryParams.getEndTime()) + "'");
        }
        sql.append(whereSql).append(orderSql).toString();
        List<EarthquakeDisasterVO> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(EarthquakeDisasterVO.class));

        return list;
    }

    @Override
    public Integer countByCounty(String name) {
        return jdbcTemplate.queryForObject("select count(a.*) from data_earthquake_disaster a,district_boundary_county t where st_intersects(st_geomfromtext('POINT('||a.ded_longitude||' '||a.ded_latitude||')',4490), t.geom) and t.name='" + name + "'and a.ded_isdelete='0'  ", Integer.class);
    }

    @Override
    public Integer countByCity(String name) {
        return jdbcTemplate.queryForObject("select count(a.*) from data_earthquake_disaster a,district_boundary_city t where st_intersects(st_geomfromtext('POINT('||a.ded_longitude||' '||a.ded_latitude||')',4490), t.geom) and t.name='" + name + "' and a.ded_isdelete='0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String name) {
        return jdbcTemplate.queryForObject("select count(a.*) from data_earthquake_disaster a,district_boundary_province t where st_intersects(st_geomfromtext('POINT('||a.ded_longitude||' '||a.ded_latitude||')',4490), t.geom) and t.name='" + name + "' and a.ded_isdelete='0' ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("select count(a.*) from data_earthquake_disaster a,district_boundary_province t where st_intersects(st_geomfromtext('POINT('||a.ded_longitude||' '||a.ded_latitude||')',4490), t.geom) and t.name=? and (TO_CHAR(ded_updatetime, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(ded_createtime, 'yyyy-MM-dd hh24:mi:ss') LIKE ?)  and a.ded_isdelete='0' ", Integer.class, provinceName, taskYear + "%", taskYear + "%");

    }

}
